High Low Stock Pivot Chart for Excel

2/15/2022

Bottom Line: Learn a workaround for creating an interactive stock chart in Excel, since the stock chart is not available for pivot charts by default.

Skill Level: Intermediate

Watch the Tutorial

 

Create an Interactive Stock Chart

We received a question from John, a member of our Elevate Excel training program. He asked about creating high low stock charts that are interactive, since stock charts aren't available for pivot charts by default.

High low stock charts are a great way to visualize stock data. The vertical lines represent the movement of stock during the day. The circular points represent the amount that the stock closed at.

These kinds of charts are available as regular charts in Excel, but not as pivot charts. However, we can create our own interactive stock chart by manipulating a line chart and adding some slicers. Here's how.

 

Make a Pivot Table

Begin by creating a pivot table from a set of stock data. To do this, select Pivot Table on the Insert tab. Select OK to create the pivot table on a New Sheet.

To set up the pivot table, put the Date field in the Rows area. Then put the High, Low, and Close fields in the Values area. And finally, place the Symbol field in the Filters area.

This set-up creates a pivot table that looks like this:

 

Create a Pivot Chart

The next step is to create a chart from the pivot table. Go to the Insert tab and select Pivot Chart, thenLine, then Line with Markers.

You can declutter the chart by hiding all the field buttons and the legend/key. (Use the right-click menu to hide or delete those.) If needed, adjust the Bounds in order to zoom in on the pertinent area. That allows you to better see the variance between highs and lows.

Next, we will be changing the formatting of the graph to remove the lines and accentuate the variance. 

Start by clicking on any of the dots in the Sum of Close series to select that series line. If that's difficult to do because your lines are too close together, you can also select it by going to the Format tab and finding Sum of Close in the first drop-down menu. 

Now that the Close line is selected, go to the Design tab and choose Add Chart Element. Choose Lines, and then High-Low Lines. 

This will add vertical lines between the high and low points of each day. 

Now we want to hide the high and low horizontal lines that connect the points for each day. To do this, first, select the High series and go to Shape Fill and select No Fill to remove the circles. Then go to Shape Line and choose No Outline to remove the lines. Repeat the circle-removal and line-removal process for the Low series.

This leaves just the Close series visible.

For the Close series, we want to remove the Outline, but not the Fill. That will leave the circles, but not the line. 

You can adjust the fill color and the vertical line thickness to your taste. You can also add a title and make any other formatting changes you might prefer. The final result can look something like this.

 

Add Slicers

If you go to the Analyze/Options tab and then choose Insert Slicer, you'll see a list of fields to choose from. Select Symbol and Month Name to create slicers for those criteria.

At this point, you should probably change the Bounds back to Auto so that you can see all of the appropriate data when you use the slicers. 

 

Source: excelcampus.com